package com.cyt.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.cyt.entity.SPFeiLei;
import com.cyt.entity.ShangPingXX;
import com.cyt.entity.XingWengXiZhi;
import com.cyt.util.DBManager;

public class ShangPingXXTwoDaoImpl {
		public List<ShangPingXX> getShangPingXXTwo(int cid ) {
			List<ShangPingXX> list = new ArrayList<ShangPingXX>();
			Statement st = null;
			ResultSet rs = null;
			Connection conn = DBManager.getConnection();
			try {
				st = conn.createStatement();
				String sql = "SELECT * FROM shangping WHERE cPid="+cid;
				rs = st.executeQuery(sql);
				while (rs.next()) { 
					int cPid = rs.getInt("cPid");
					String cPName = rs.getString("cPName");
					String cPCaiZhi = rs.getString("cPCaiZhi");
					String cPPingPai = rs.getString("cPPingPai");
					String xingHao = rs.getString("xingHao");
					String yanSe = rs.getString("yanSe");
					String dingZhi = rs.getString("dingZhi");
					String chuanYiRenQun = rs.getString("chuanYiRenQun");
					int cPShoujia = rs.getInt("cPShoujia");
					int cPShiChangSJ = rs.getInt("cPShiChangSJ");
					int fengLeiId = rs.getInt("fengLeiId");
					String cPImg = rs.getString("cPImg");
					ShangPingXX spxxTwo = new ShangPingXX(cPid, cPName, cPCaiZhi, cPShoujia, cPShiChangSJ, cPPingPai, xingHao, yanSe, dingZhi, chuanYiRenQun, fengLeiId, cPImg);// 创建用户对象
					list.add(spxxTwo);
				}
				return list;
	         
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			} finally {
				DBManager.closeAll(conn, st, rs);
			}

		}
		
		
		/**
		 * 多条件 分页查产品
		 * 
		 * @return
		 */
		public List<ShangPingXX> getShangPinXiangQinPage(ShangPingXX sp ,int page, int size){//一般看sql语句有没有条件
			List<ShangPingXX> list = new ArrayList<ShangPingXX>();
			Statement st = null;
			ResultSet rs = null;
			Connection conn = DBManager.getConnection();
			try {
				st = conn.createStatement();
				
				String sql = "SELECT s.*,sp.sPFengLeiName FROM shangping s,spfenglei sp where 1=1";
				
				if(sp.getcPid()!=0) {
					sql+= " and cPid ="+sp.getcPid();
					
				}  
				if(sp.getcPName()!=null && !sp.getcPName().equals("")) {
					sql+= " and cPName like '%"+sp.getcPName()+"%'";
					
				}
				if(sp.getcPCaiZhi()!=null && !sp.getcPCaiZhi().equals("")) {
					sql+= " and cPCaiZhi like '%"+sp.getcPCaiZhi()+"%'";
					
				}
				if(sp.getQiShiCPShoujia()!=0) {
					sql+= " and cPShoujia>"+sp.getQiShiCPShoujia();
					
				}
				if(sp.getJieShuCPShoujia()!=0) {
					sql+= " and cPShoujia<"+sp.getJieShuCPShoujia();
					
				}
				if(sp.getcPPingPai()!=null && !sp.getcPPingPai().equals("")) {
					sql+= " and cPPingPai like '%"+sp.getcPPingPai()+"%'";
					
				}
				if(sp.getYanSe()!=null && !sp.getYanSe().equals("")) {
					sql+= " and yanSe like '%"+sp.getYanSe()+"%'";
					
				}
				if(sp.getFengLeiId()!=0) {  
					sql+= " and fengLeiId="+sp.getFengLeiId();
					
				}
				sql+=" and s.fengLeiId=sp.sPFengLeiId LIMIT "+(page-1)*size+","+size;//产品分类
				
				System.out.println(sql);
				rs = st.executeQuery(sql);
				while (rs.next()) { 
					int cPid = rs.getInt("cPid");
					String cPName = rs.getString("cPName");
					String cPCaiZhi = rs.getString("cPCaiZhi");
					String cPPingPai = rs.getString("cPPingPai");
					String xingHao = rs.getString("xingHao");
					String yanSe = rs.getString("yanSe");
					String dingZhi = rs.getString("dingZhi");
					String chuanYiRenQun = rs.getString("chuanYiRenQun");
					int cPShoujia = rs.getInt("cPShoujia");
					int cPShiChangSJ = rs.getInt("cPShiChangSJ");
					int fengLeiId = rs.getInt("fengLeiId");
					String cPImg = rs.getString("cPImg");
					String sPFengLeiName = rs.getString("sPFengLeiName");
					ShangPingXX spxxTwo = new ShangPingXX(cPid, cPName, cPCaiZhi, cPShoujia, 0, 0, cPShiChangSJ, cPPingPai, xingHao, yanSe, dingZhi, chuanYiRenQun, fengLeiId, cPImg, sPFengLeiName);// 创建用户对象
					list.add(spxxTwo);
				}
				return list;
	         
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			} finally {
				DBManager.closeAll(conn, st, rs);
			}

		}
		
		/**
		 * 查询所有商品的所有信息
		 * @param sp
		 * @param page
		 * @param size
		 * @return
		 */
		public List<ShangPingXX> getShangPinXiangQin(ShangPingXX sp){//一般看sql语句有没有条件
			List<ShangPingXX> list = new ArrayList<ShangPingXX>();
			Statement st = null;
			ResultSet rs = null;
			Connection conn = DBManager.getConnection();
			try {
				st = conn.createStatement();
				
				String sql = "SELECT s.*,sp.sPFengLeiName FROM shangping s,spfenglei sp where 1=1";//1=1是永真条件,为了防止下面拼接sql语句条件时,与and冲突
				
				if(sp.getcPid()!=0) {
					sql+= " and cPid ="+sp.getcPid();
					
				}  
				if(sp.getcPName()!=null && !sp.getcPName().equals("")) {
					sql+= " and cPName like '%"+sp.getcPName()+"%'";
					
				}
				if(sp.getcPCaiZhi()!=null && !sp.getcPCaiZhi().equals("")) {
					sql+= " and cPCaiZhi like '%"+sp.getcPCaiZhi()+"%'";
					
				}
				if(sp.getQiShiCPShoujia()!=0) {
					sql+= " and cPShoujia>"+sp.getQiShiCPShoujia();
					
				}
				if(sp.getJieShuCPShoujia()!=0) {
					sql+= " and cPShoujia<"+sp.getJieShuCPShoujia();
					
				}
				if(sp.getcPPingPai()!=null && !sp.getcPPingPai().equals("")) {
					sql+= " and cPPingPai like '%"+sp.getcPPingPai()+"%'";
					
				}
				if(sp.getYanSe()!=null && !sp.getYanSe().equals("")) {
					sql+= " and yanSe like '%"+sp.getYanSe()+"%'";
					
				}
				if(sp.getFengLeiId()!=0) {  
					sql+= " and fengLeiId="+sp.getFengLeiId();
					
				}
				sql+=" and s.fengLeiId=sp.sPFengLeiId";
				
				System.out.println(sql);
				rs = st.executeQuery(sql);
				while (rs.next()) { 
					int cPid = rs.getInt("cPid");
					String cPName = rs.getString("cPName");
					String cPCaiZhi = rs.getString("cPCaiZhi");
					String cPPingPai = rs.getString("cPPingPai");
					String xingHao = rs.getString("xingHao");
					String yanSe = rs.getString("yanSe");
					String dingZhi = rs.getString("dingZhi");
					String chuanYiRenQun = rs.getString("chuanYiRenQun");
					int cPShoujia = rs.getInt("cPShoujia");
					int cPShiChangSJ = rs.getInt("cPShiChangSJ");
					int fengLeiId = rs.getInt("fengLeiId");
					String cPImg = rs.getString("cPImg");
					String sPFengLeiName = rs.getString("sPFengLeiName");
					ShangPingXX spxxTwo = new ShangPingXX(cPid, cPName, cPCaiZhi, cPShoujia, 0, 0, cPShiChangSJ, cPPingPai, xingHao, yanSe, dingZhi, chuanYiRenQun, fengLeiId, cPImg, sPFengLeiName);// 创建用户对象
					list.add(spxxTwo);
				}
				return list;
	         
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			} finally {
				DBManager.closeAll(conn, st, rs);
			}

		}
		
		
		/**
		 * 通过主键删除产品
		 * @param shangId  返回的产品id 即要删除的
		 * @return
		 */
		public int shanChuChanPinZhu(int shangId) {
			return DBManager.executeUpdate("DELETE FROM shangping WHERE cPid="+shangId);
		}
		
		
		/**
		 * 通过id主键删除
	     * 批量删除
		 * @param 
		 * @return
		 */
		public int shanChanPinXQS(String ids) {
			String sql = "delete from shangping where cPid in("+ids+")";
			System.out.println(sql);
			return DBManager.executeUpdate(sql);
		}
		
		/**
		 * 添加产品
		 * @param spxq
		 * @return
		 */
		public int addChanPinXQ(ShangPingXX spxq ) {
			return DBManager.executeUpdate("INSERT into shangping VALUES(null,'"+spxq.getcPName()+"','"+spxq.getcPCaiZhi()+"',"+spxq.getcPShoujia()+","+spxq.getcPShiChangSJ()+",'"+spxq.getcPPingPai()+"','"+spxq.getXingHao()+"','"+spxq.getYanSe()+"','"+spxq.getDingZhi()+"','"+spxq.getChuanYiRenQun()+"',"+spxq.getFengLeiId()+",'"+spxq.getcPImg()+"')");
		}
		
		
		/**
		 * 根据id查商品详细信息,以供修改的数据回显
		 * @param spId
		 * @return
		 */
		public ShangPingXX chaShangPingXXId(int spId) {
			ShangPingXX xwxz=null;
			Statement st = null;
			ResultSet rs = null;
			Connection conn = DBManager.getConnection();
			try {
				st = conn.createStatement();
				String sql = "SELECT * FROM shangping WHERE cPid="+spId;
				rs = st.executeQuery(sql);
				while (rs.next()) {
					int cPid = rs.getInt("cPid");
					String cPName = rs.getString("cPName");
					String cPCaiZhi = rs.getString("cPCaiZhi");
					String cPPingPai = rs.getString("cPPingPai");
					String xingHao = rs.getString("xingHao");
					String yanSe = rs.getString("yanSe");
					String dingZhi = rs.getString("dingZhi");
					String chuanYiRenQun = rs.getString("chuanYiRenQun");
					int cPShoujia = rs.getInt("cPShoujia");
					int cPShiChangSJ = rs.getInt("cPShiChangSJ");
					int fengLeiId = rs.getInt("fengLeiId");
					String cPImg = rs.getString("cPImg");

				xwxz = new ShangPingXX(cPid, cPName, cPCaiZhi, cPShoujia, cPShiChangSJ, cPPingPai, xingHao, yanSe, dingZhi, chuanYiRenQun, fengLeiId, cPImg);
					
				}
				return xwxz;

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			} finally {
				DBManager.closeAll(conn, st, rs);
			}

		}
		
		
		/**
		 * 修改产品  根据id
		 * @param spxx
		 * @return
		 */
		public int xiuGaiShangPingXX(ShangPingXX spxx ) {
			String sql="UPDATE shangping set cPName='"+spxx.getcPName()+"',cPCaiZhi='"+spxx.getcPCaiZhi()+"',cPShoujia="+spxx.getcPShoujia()+",cPShiChangSJ="+spxx.getcPShiChangSJ()+",cPPingPai='"+spxx.getcPPingPai()+"',xingHao='"+spxx.getXingHao()+"',yanSe='"+spxx.getYanSe()+"',dingZhi='"+spxx.getDingZhi()+"',chuanYiRenQun='"+spxx.getChuanYiRenQun()+"',fengLeiId="+spxx.getFengLeiId()+",cPImg='"+spxx.getcPImg()+"' where cPid="+spxx.getcPid();
			System.out.println(sql);
			return DBManager.executeUpdate(sql);
		}
}
